%set up for database
host = 'localhost';
user = 'root';
password = '123456';
dbName = 'quant_data';
dbConn = database(dbName, user, password, ...
    'com.mysql.jdbc.Driver',sprintf('jdbc:mysql://localhost/%s',dbName));


%create log file
fileID = fopen('log_database68_data.txt','w');
fprintf(fileID,'--------START %s -----------\n',datestr(now));

%read file in directory \data
dirName = 'data2';
dirData = dir(dirName);      %# Get the data for the current directory
dirIndex = [dirData.isdir];  %# Find the index for directories
fileList = {dirData(~dirIndex).name}';  %'# Get a list of the files

for k = 1: length(fileList)
    
    %read file csv
    pathFile = [dirName '\' fileList{k}];
    [num,txt,raw] = xlsread(pathFile);
    %get the ticker
    attr = regexp(fileList{k}, '[-.]', 'split');
    ticker = attr{1};    
    if(isempty(ticker))
        continue;
    end
    
    [n m] = size(num);
    security_id = get_info_from_database('sec_id',ticker);
    if(m == 1)
        fprintf('No data %s \n',ticker);
        fprintf(fileID,'No data %s \n',ticker);
        continue;
    end
    if(~isnumeric(security_id))
        fprintf('Can not find stock with name %s \n',ticker);
        fprintf(fileID,'Can not find stock with name %s \n',ticker);
        continue;
    end
    
    if(isempty(num))
        fprintf('%s no data with n = %d \n',ticker,n);        
        fprintf(fileID,'%s no data with n = %d \n',ticker,n);        
        continue;
    end
    
    %sql template for import
    sql_template_bs = ['INSERT INTO `balance_sheet_cp68` VALUES (0,%d,%d,%d'...
                     ',%f,%f,%f,%f,%f,%f,%f,%f,%f,%f'...
                     ',%f,%f,%f,%f,%f,%f,%f,%f,%f,%f'...
                     ',%f,%f,1)'];
    sql_template_is = ['INSERT INTO `income_statement_cp68` VALUES (0,%d,%d,%d'...
                     ',%f,%f,%f,%f,%f,%f,%f,%f,%f,%f'...
                     ',%f,%f,%f,%f,%f,%f,1)'];

    %data process
    [year, quarter]= cellfun(@getYearQuarter, raw(1,2:end), 'UniformOutput', false);
    year2 = cell2mat(year);
    
    year_1 = [];
    quarter_1 = [];
    
    if(all(isnan(year2)))
        fprintf('%s Different format with n = %d \n',ticker,n);        
        fprintf(fileID,'%s Different format with n = %d \n',ticker,n);        
        continue;
    end
    processed_is = [];
    processed_bs = num([2:10 14:26],:);        
    switch(n)
        case {51,52} % normal        
            processed_is = num(33:48,:);
            yq_is = raw(32,2:end);
        case 77 % bank
            processed_is = num(53:68,:);
            yq_is = raw(52,2:end);
        case 66 % 
            processed_is = num(33:48,:);
            yq_is = raw(32,2:end);
        otherwise
            fprintf('%s Do not match column with n = %d \n',ticker,n);        
            fprintf(fileID,'%s Do not match column with n = %d \n',ticker,n);        
    end
    
    [year_1 quarter_1]= cellfun(@getYearQuarter, yq_is, 'UniformOutput', false);

    %import
    for i = 1: m
        if(~isnan(year{i}))
            temp = sprintf(sql_template_bs,[year{i} quarter{i} security_id processed_bs(:,i)']);        
            sql = strrep(temp, ',NaN', ',null');        
            x = exec(dbConn, sql);
        end
        
        if(~isnan(year_1{i}))
            temp = sprintf(sql_template_is,[year_1{i} quarter_1{i} security_id processed_is(:,i)']);
            sql = strrep(temp, ',NaN', ',null');
            x = exec(dbConn, sql);
        end
        
    end

    fprintf('Done Importing %d/%d %s with n=%d \n',k,length(fileList),ticker,n);
    fprintf(fileID,'Done Importing %d/%d %s with n=%d \n',k,length(fileList),ticker,n);    
end


fprintf(fileID,'--------END %s -----------\n',datestr(now));
fclose(fileID);